﻿CREATE PROCEDURE [dbo].[proc_InOutSide_TongJi]
	(
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@CompanyId int,
		@DepartmentId int
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@departmentid

	Select d.Id,d.ParentId,
	(case when len(bmbh)>4 then replicate('┆┄┄',(len(bmbh)-4)/2)+isnull(dname,'') else '◆'+isnull(dname,'') end) as DName,
	(Select count(0) From InOutSide Where DepId=d.id and If_Date IS NOT NULL and If_Date>=@sRq And If_Date<=@eRq) As yizhuanchu, --已转出
	(Select count(0) From CheckInOut Where departmentid=d.id and resultFlag=1 and CheckOutTime>=@sRq And CheckOutTime<=@eRq) as Effective, --有效
	(Select count(0) From CheckInOut Where departmentid=d.id and resultFlag=2 and CheckOutTime>=@sRq And CheckOutTime<=@eRq) as Invalid, --无效
	(Select count(0) From CheckInOut Where departmentid=d.id and resultFlag=0 and CheckOutTime>=@sRq And CheckOutTime<=@eRq AND CheckInOut.CheckInTime IS NULL ) as NotSign --未签回
	From Department d
	Where CompanyId = @companyid And Left(bmbh,Len(@bmbh_T))=@bmbh_T
	Order By D.bmbh
End